﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using IPR_StudyMeetingMrgSys.DBUtility;//Please add references
using System.Collections.Generic;
using IPR_StudyMeetingMrgSys.Model;
using System.Linq;

namespace IPR_StudyMeetingMrgSys.DAL
{
	/// <summary>
	/// 数据访问类:TrainCourse
	/// </summary>
	public partial class TrainCourse
	{
		public TrainCourse()
		{}
		#region  BasicMethod



		/// <summary>
		/// 增加一条数据
		/// </summary>
		public long Add(IPR_StudyMeetingMrgSys.Model.TrainCourse model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into TrainCourse(");
			strSql.Append("TPID,TCID,TCOID,TrainTeacherID,TrainTeacherName,StartDateTime,EndDateTime,Duration,DepID,IsCheck,IsRelease,AddUserID,AddUserName,AddDateTime,IsUse,DepLevel,SendDepID,CRID,CRName,TCOName)");
			strSql.Append(" values (");
			strSql.Append("@TPID,@TCID,@TCOID,@TrainTeacherID,@TrainTeacherName,@StartDateTime,@EndDateTime,@Duration,@DepID,@IsCheck,@IsRelease,@AddUserID,@AddUserName,@AddDateTime,@IsUse,@DepLevel,@SendDepID,@CRID,@CRName,@TCOName)");
			strSql.Append(";select @@IDENTITY");
			SqlParameter[] parameters = {
					new SqlParameter("@TPID", SqlDbType.BigInt,8),
					new SqlParameter("@TCID", SqlDbType.BigInt,8),
					new SqlParameter("@TCOID", SqlDbType.BigInt,8),
					new SqlParameter("@TrainTeacherID", SqlDbType.NVarChar,20),
					new SqlParameter("@TrainTeacherName", SqlDbType.NVarChar,20),
					new SqlParameter("@StartDateTime", SqlDbType.DateTime,8),
					new SqlParameter("@EndDateTime", SqlDbType.DateTime,8),
					new SqlParameter("@Duration", SqlDbType.Int,4),
					new SqlParameter("@DepID", SqlDbType.Int,4),
					new SqlParameter("@IsCheck", SqlDbType.Int,4),
					new SqlParameter("@IsRelease", SqlDbType.Int,4),
					new SqlParameter("@AddUserID", SqlDbType.NVarChar,20),
					new SqlParameter("@AddUserName", SqlDbType.NVarChar,20),
					new SqlParameter("@AddDateTime", SqlDbType.DateTime,8),
					new SqlParameter("@IsUse", SqlDbType.Int,4),
                    new SqlParameter("@DepLevel", SqlDbType.Int,4),
                    new SqlParameter("@SendDepID", SqlDbType.NVarChar,300),
                    new SqlParameter("@CRID", SqlDbType.Int,4),
                    new SqlParameter("@CRName", SqlDbType.NVarChar,50),
                    new SqlParameter("@TCOName", SqlDbType.NVarChar,100)};
			parameters[0].Value = model.TPID;
			parameters[1].Value = model.TCID;
			parameters[2].Value = model.TCOID;
			parameters[3].Value = model.TrainTeacherID;
			parameters[4].Value = model.TrainTeacherName;
			parameters[5].Value = model.StartDateTime;
			parameters[6].Value = model.EndDateTime;
			parameters[7].Value = model.Duration;
			parameters[8].Value = model.DepID;
			parameters[9].Value = model.IsCheck;
			parameters[10].Value = model.IsRelease;
			parameters[11].Value = model.AddUserID;
			parameters[12].Value = model.AddUserName;
			parameters[13].Value = model.AddDateTime;
			parameters[14].Value = model.IsUse;
            parameters[15].Value = model.DepLevel;
            parameters[16].Value = model.SendDepID;
            parameters[17].Value = model.CRID;
            parameters[18].Value = model.CRName;
            parameters[19].Value = model.TCOName;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(),parameters);
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt64(obj);
			}
		}
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(IPR_StudyMeetingMrgSys.Model.TrainCourse model,int? depLevel,out string errorMsg)
        {
            bool isContinue = true; errorMsg = string.Empty;
                IPR_StudyMeetingMrgSys.Model.TrainCourse obj = new IPR_StudyMeetingMrgSys.Model.TrainCourse();
                StringBuilder strSql_select = new StringBuilder();
                strSql_select.Append("select  top 1 DepLevel,IsRelease,IsCheck from TrainCourse ");
                strSql_select.Append(" where TCOID=@TCOID");
                SqlParameter[] parameters_select = {
                    new SqlParameter("@TCOID", SqlDbType.BigInt)
            };
                parameters_select[0].Value = model.TCOID;
                DataSet ds = DbHelperSQL.Query(strSql_select.ToString(), parameters_select);
            if (ds.Tables[0].Rows.Count > 0)
                {
                    var item = ds.Tables[0].Rows[0];
                if (item["DepLevel"] != null && item["DepLevel"].ToString() != "")
                {
                    if (depLevel>Convert.ToInt32(item["DepLevel"].ToString()))
                    {
                        errorMsg = "1";
                        return true; 
                    }
                }
                  
                    if (item["IsCheck"] != null && item["IsCheck"].ToString() != "")
                    {
                        if (int.Parse(item["IsCheck"].ToString()) > 0)
                        {
                            isContinue = false; errorMsg = "已审核不能编辑";
                        }
                    }
                }
          
            if (isContinue)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update TrainCourse set ");
                strSql.Append("TPID=@TPID,");
                strSql.Append("TCID=@TCID,");
                strSql.Append("TCOID=@TCOID,");
                strSql.Append("TrainTeacherID=@TrainTeacherID,");
                strSql.Append("TrainTeacherName=@TrainTeacherName,");
                strSql.Append("StartDateTime=@StartDateTime,");
                strSql.Append("EndDateTime=@EndDateTime,");
                strSql.Append("Duration=@Duration,");
                //strSql.Append("DepID=@DepID,");
                //strSql.Append("IsCheck=@IsCheck,");
                //strSql.Append("IsRelease=@IsRelease,");
                strSql.Append("AddUserID=@AddUserID,");
                strSql.Append("AddUserName=@AddUserName,");
                strSql.Append("AddDateTime=@AddDateTime,");
                //strSql.Append("IsUse=@IsUse,");
                //strSql.Append("DepLevel=@DepLevel,");
                strSql.Append("SendDepID=@SendDepID,");
                strSql.Append("CRID=@CRID,");
                strSql.Append("CRName=@CRName,");
                strSql.Append("TCOName=@TCOName ");
                strSql.Append(" where TCOID=@TCOID");
                SqlParameter[] parameters = {
                    new SqlParameter("@TPID", SqlDbType.BigInt,8),
                    new SqlParameter("@TCID", SqlDbType.BigInt,8),
                    new SqlParameter("@TCOID", SqlDbType.BigInt,8),
                    new SqlParameter("@TrainTeacherID", SqlDbType.NVarChar,20),
                    new SqlParameter("@TrainTeacherName", SqlDbType.NVarChar,20),
                    new SqlParameter("@StartDateTime", SqlDbType.NVarChar,20),
                    new SqlParameter("@EndDateTime", SqlDbType.NVarChar,20),
                    new SqlParameter("@Duration", SqlDbType.Int,4),
                    //new SqlParameter("@DepID", SqlDbType.Int,4),
                    //new SqlParameter("@IsCheck", SqlDbType.Int,4),
                    //new SqlParameter("@IsRelease", SqlDbType.Int,4),
                    new SqlParameter("@AddUserID", SqlDbType.NVarChar,20),
                    new SqlParameter("@AddUserName", SqlDbType.NVarChar,20),
                    new SqlParameter("@AddDateTime", SqlDbType.NVarChar,20),
                    //new SqlParameter("@IsUse", SqlDbType.Int,4),
                    //new SqlParameter("@DepLevel", SqlDbType.Int,4),
                    new SqlParameter("@AutoID", SqlDbType.BigInt,8),
                    new SqlParameter("@SendDepID", SqlDbType.NVarChar,300),
                    new SqlParameter("@CRID", SqlDbType.BigInt,8),
                    new SqlParameter("@CRName", SqlDbType.NVarChar,50),
                    new SqlParameter("@TCOName", SqlDbType.NVarChar,100)};
                parameters[0].Value = model.TPID;
                parameters[1].Value = model.TCID;
                parameters[2].Value = model.TCOID;
                parameters[3].Value = model.TrainTeacherID;
                parameters[4].Value = model.TrainTeacherName;
                parameters[5].Value = model.StartDateTime;
                parameters[6].Value = model.EndDateTime;
                parameters[7].Value = model.Duration;
                //parameters[8].Value = model.DepID;
                //parameters[8].Value = model.IsCheck;
                //parameters[9].Value = model.IsRelease;
                parameters[8].Value = model.AddUserID;
                parameters[9].Value = model.AddUserName;
                parameters[10].Value = model.AddDateTime;
                //parameters[11].Value = model.IsUse;
                //parameters[12].Value = model.DepLevel;
                parameters[11].Value = model.AutoID;
                parameters[12].Value = model.SendDepID;
                parameters[13].Value = model.CRID;
                parameters[14].Value = model.CRName;
                parameters[15].Value = model.TCOName;

                int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
                isContinue = rows > 0 ? true : false;
            }
            return isContinue;
        }

        /// <summary>
        /// 课程更新教师
        /// </summary>
        public bool UpdateTeacher(string tcoid,string teacherid,string teachername)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("update TrainCourse set TrainTeacherID='"+teacherid+"' ,TrainTeacherName='"+teachername+"' where TCOID="+tcoid); 
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(long AutoID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from TrainCourse ");
			strSql.Append(" where AutoID=@AutoID");
			SqlParameter[] parameters = {
					new SqlParameter("@AutoID", SqlDbType.BigInt)
			};
			parameters[0].Value = AutoID;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 批量删除数据
		/// </summary>
		public bool DeleteList(string TCOIDlist)
		{
			StringBuilder strSql=new StringBuilder();

            var listid = "0";
            StringBuilder strSql_select = new StringBuilder();
            strSql_select.Append("select  top 1 TCOID from TrainCourse ");
            strSql_select.Append(" where TCOID in (" + TCOIDlist + ") and IsCheck='0' and IsRelease='0' ");
            DataSet ds = DbHelperSQL.Query(strSql_select.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                //var item = ds.Tables[0].Rows[0];
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    var item = ds.Tables[0].Rows[i];
                    if (item["TCOID"] != null && item["TCOID"].ToString() != "")
                    {
                        if (int.Parse(item["TCOID"].ToString()) > 0)
                        {
                            listid += "," + item["TCOID"].ToString() + "";
                        }
                    }
                }
            }

            strSql.Append("delete from TrainCourseUser ");
            strSql.Append(" where TCOID in (" + listid + ")  ");
            strSql.Append("delete from TrainCourse ");
			strSql.Append(" where TCOID in ("+ listid + ")  ");
            strSql.Append("delete from TrainCourseFiles ");
            strSql.Append(" where TCOID in (" + listid + ")  ");
            int rows=DbHelperSQL.ExecuteSql(strSql.ToString());
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public IPR_StudyMeetingMrgSys.Model.TrainCourse GetModel(long TCOID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select  top 1 AutoID,TPID,TCID,TCOID,TrainTeacherID,TrainTeacherName,StartDateTime,EndDateTime,Duration,DepID,IsCheck,IsRelease,AddUserID,AddUserName,AddDateTime,IsUse,DepLevel,CRID,CRName,SendDepID,TCOName from TrainCourse ");
			strSql.Append(" where TCOID=@TCOID");
			SqlParameter[] parameters = {
					new SqlParameter("@TCOID", SqlDbType.BigInt)
			};
			parameters[0].Value = TCOID;

			IPR_StudyMeetingMrgSys.Model.TrainCourse model=new IPR_StudyMeetingMrgSys.Model.TrainCourse();
			DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				return DataRowToModel(ds.Tables[0].Rows[0]);
			}
			else
			{
				return null;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public IPR_StudyMeetingMrgSys.Model.TrainCourse DataRowToModel(DataRow row)
		{
			IPR_StudyMeetingMrgSys.Model.TrainCourse model=new IPR_StudyMeetingMrgSys.Model.TrainCourse();
			if (row != null)
			{
				if(row["AutoID"]!=null && row["AutoID"].ToString()!="")
				{
					model.AutoID=long.Parse(row["AutoID"].ToString());
				}
				if(row["TPID"]!=null && row["TPID"].ToString()!="")
				{
					model.TPID=long.Parse(row["TPID"].ToString());
				}
				if(row["TCID"]!=null && row["TCID"].ToString()!="")
				{
					model.TCID=long.Parse(row["TCID"].ToString());
				}
				if(row["TCOID"]!=null && row["TCOID"].ToString()!="")
				{
					model.TCOID=long.Parse(row["TCOID"].ToString());
				}
				if(row["TrainTeacherID"]!=null)
				{
					model.TrainTeacherID=row["TrainTeacherID"].ToString();
				}
				if(row["TrainTeacherName"]!=null)
				{
					model.TrainTeacherName=row["TrainTeacherName"].ToString();
				}
				if(row["StartDateTime"]!=null)
				{
					model.StartDateTime=Convert.ToDateTime( row["StartDateTime"].ToString());
				}
				if(row["EndDateTime"]!=null)
				{
					model.EndDateTime=Convert.ToDateTime( row["EndDateTime"].ToString());
				}
				if(row["Duration"]!=null && row["Duration"].ToString()!="")
				{
					model.Duration=int.Parse(row["Duration"].ToString());
				}
				if(row["DepID"]!=null && row["Duration"].ToString() != "")
                {
					model.DepID=int.Parse( row["DepID"].ToString());
				}
				if(row["IsCheck"]!=null && row["IsCheck"].ToString()!="")
				{
					model.IsCheck=int.Parse(row["IsCheck"].ToString());
				}
				if(row["IsRelease"]!=null && row["IsRelease"].ToString()!="")
				{
					model.IsRelease=int.Parse(row["IsRelease"].ToString());
				}
				if(row["AddUserID"]!=null)
				{
					model.AddUserID=row["AddUserID"].ToString();
				}
				if(row["AddUserName"]!=null)
				{
					model.AddUserName=row["AddUserName"].ToString();
				}
				if(row["AddDateTime"]!=null)
				{
					model.AddDateTime=Convert.ToDateTime( row["AddDateTime"].ToString());
				}
				if(row["IsUse"]!=null && row["IsUse"].ToString()!="")
				{
					model.IsUse=int.Parse(row["IsUse"].ToString());
                }
                if (row["DepLevel"] != null && row["DepLevel"].ToString() != "")
                {
                    model.DepLevel = int.Parse(row["DepLevel"].ToString());
                }
                if (row["CRID"] != null && row["CRID"].ToString() != "")
                {
                    model.CRID = int.Parse(row["CRID"].ToString());
                }
                if (row["CRName"] != null)
                {
                    model.CRName = row["CRName"].ToString();
                }
                if (row["SendDepID"] != null)
                {
                    model.SendDepID = row["SendDepID"].ToString();
                }
                if (row["TCOName"] != null)
                {
                    model.TCOName = row["TCOName"].ToString();
                }
            }
			return model;
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select AutoID,TPID,TCID,TCOID,TrainTeacherID,TrainTeacherName,StartDateTime,EndDateTime,Duration,DepID,IsCheck,IsRelease,AddUserID,AddUserName,AddDateTime,IsUse,DepLevel,CRID,CRName,SendDepID,TCOName ");
			strSql.Append(" FROM TrainCourse ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" AutoID,TPID,TCID,TCOID,TrainTeacherID,TrainTeacherName,StartDateTime,EndDateTime,Duration,DepID,IsCheck,IsRelease,AddUserID,AddUserName,AddDateTime,IsUse,DepLevel,CRID,CRName,SendDepID,TCOName ");
			strSql.Append(" FROM TrainCourse ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获取记录总数
		/// </summary>
		public int GetRecordCount(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) FROM TrainCourse ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			object obj = DbHelperSQL.GetSingle(strSql.ToString());
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("SELECT * FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby );
			}
			else
			{
				strSql.Append("order by T.AutoID desc");
			}
			strSql.Append(")AS Row, T.*  from TrainCourse T ");
			if (!string.IsNullOrEmpty(strWhere.Trim()))
			{
				strSql.Append(" WHERE " + strWhere);
			}
			strSql.Append(" ) TT");
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
			return DbHelperSQL.Query(strSql.ToString());
		}

        /*
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize,int PageIndex,string strWhere)
		{
			SqlParameter[] parameters = {
					new SqlParameter("@tblName", SqlDbType.VarChar, 255),
					new SqlParameter("@fldName", SqlDbType.VarChar, 255),
					new SqlParameter("@PageSize", SqlDbType.Int),
					new SqlParameter("@PageIndex", SqlDbType.Int),
					new SqlParameter("@IsReCount", SqlDbType.Bit),
					new SqlParameter("@OrderType", SqlDbType.Bit),
					new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
					};
			parameters[0].Value = "TrainCourse";
			parameters[1].Value = "AutoID";
			parameters[2].Value = PageSize;
			parameters[3].Value = PageIndex;
			parameters[4].Value = 0;
			parameters[5].Value = 0;
			parameters[6].Value = strWhere;	
			return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
		}*/

        #endregion  BasicMethod
        #region  ExtensionMethod

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex, out int rowCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.AutoID desc");
            }
            strSql.Append(")AS Row, T.*  from TrainCourse T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");

            rowCount = 0;
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds != null)
                if (ds.Tables.Count > 0)
                    if (ds.Tables[0].Rows.Count > 0)
                        rowCount = ds.Tables[0].Rows.Count;

            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            //strSql.Append(" order by AddDateTime desc ");
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 是否存在该记录,名称是否重复
        /// </summary>
        public bool Exists(long crid,string startdatetime,string enddatetime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from TrainCourse");
            strSql.Append(" where CRID=@CRID AND (StartDateTime>='"+startdatetime+ "' and EndDateTime<='"+enddatetime+"')");
            SqlParameter[] parameters = {
                    new SqlParameter("@CRID", SqlDbType.BigInt,8)
            };
            parameters[0].Value = crid;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 培训计划发布
        /// </summary>
        public bool Release(string TPIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" update TrainCourse set IsRelease=1 ");
            strSql.Append(" where TCOID in (" + TPIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取当前教室最近的培训课程列表
        /// </summary>
        /// <param name="CRID"></param>
        /// <returns></returns>
        public DataSet GetNearestCourse(string CRID)
        {

            SqlParameter[] parameters = {
                    new SqlParameter("@CRID", SqlDbType.Int,4)
            };
            parameters[0].Value = CRID;
            return DbHelperSQL.RunProcedure("sp_GetCurrentTrainCourse",parameters,"course");
        }


        /// <summary>
        /// 获取当前教室最近的培训课程列表
        /// </summary>
        /// <param name="CRID"></param>
        /// <returns></returns>
        public DataSet GetLasterCourse(string CRID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select t1.* ,t2.TPName,t3.TContent, SUBSTRING( CONVERT(varchar,StartDateTime,120),12,5) +'-'+SUBSTRING( CONVERT(varchar,EndDateTime,120),12,5) coursetime  from TrainCourse t1   left join TrainPlan t2 on t1.TPID = t2.TPID   left join TrainContent t3 on t1.TCID = t3.TCID    where t1.TPID = (   select top 1 TPID from TrainCourse where CRID = " + CRID + " and CONVERT(varchar, GETDATE(),23)= convert(varchar, StartDateTime, 23) order by StartDateTime  )   order by StartDateTime ");
            //strSql.Append(" FROM TrainCourse "); 
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 主页 课程人员 已学习 未学习统计
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public string GetCourseStudy(string strWhere)
        {
            int yc = 0;
            int tc = 0;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT TCOID from TrainCourse ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            DataSet dataSet = DbHelperSQL.Query(strSql.ToString());
            string tcoids = "0";
            if (dataSet != null && dataSet.Tables[0] != null && dataSet.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow item in dataSet.Tables[0].Rows)
                {
                    if (item["TCOID"] != null && !string.IsNullOrEmpty(item["TCOID"].ToString()))
                    {
                        tcoids += "," + item["TCOID"].ToString() + "";
                    }
                }
            }
                DataSet y1 = DbHelperSQL.Query("select COUNT(*) as y from TrainStudyLog where TCOID in(" + tcoids + ") GROUP BY TCOID");
                if (y1 != null && y1.Tables[0] != null && y1.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow item in y1.Tables[0].Rows)
                    {
                        if (item["y"] != null && !string.IsNullOrEmpty(item["y"].ToString()))
                        {
                            yc += Convert.ToInt32(item["y"].ToString());
                        }
                    }
                }
            
                DataSet y = DbHelperSQL.Query("select COUNT(*) as y from TrainCourseUser where TCOID in(" + tcoids + ") GROUP BY TCOID");
                if (y != null && y.Tables[0] != null && y.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow item in y.Tables[0].Rows)
                    {
                        if (item["y"] != null && !string.IsNullOrEmpty(item["y"].ToString()))
                        {
                            tc += Convert.ToInt32(item["y"].ToString());
                        }
                    }
                }
          
            return tc + "," + yc;
        }
        /// <summary>
        /// 本月 车队/车间 学习未达标 已达标 人员统计
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public List<ReportCourseStudy> GetCourseStudyReaching(string strWhere,bool IsDuan,int? CurrentDepID)
        {
           
            string tcoids = "0";
            string tdepids = ""+CurrentDepID+"";
            #region 课程
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT TCOID from TrainCourse");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            if (!IsDuan)
            {
                #region 下级部门 及本部门 id
                string sql = "select DepID from Dep where DepPID = '"+ CurrentDepID+ "'";
                DataSet dataSetdep = DbHelperSQL.Query(sql);
                if (dataSetdep != null && dataSetdep.Tables[0] != null && dataSetdep.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow item in dataSetdep.Tables[0].Rows)
                    {
                        if (item["DepID"] != null && !string.IsNullOrEmpty(item["DepID"].ToString()))
                        {
                            tdepids += "," + item["DepID"].ToString() + "";
                        }
                    }
                }
                if (strWhere.Trim() != "")
                {
                    strSql.Append(" and  ");
                }
                strSql.Append(" DepID in (" + tdepids + ") ");
                #endregion
            }
            DataSet dataSet = DbHelperSQL.Query(strSql.ToString());
            if (dataSet != null && dataSet.Tables[0] != null && dataSet.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow item in dataSet.Tables[0].Rows)
                {
                    if (item["TCOID"] != null && !string.IsNullOrEmpty(item["TCOID"].ToString()))
                    {
                        tcoids += "," + item["TCOID"].ToString() + "";
                    }
                }
            }
            #endregion

            #region 需要学习的部门 人员总数
            DataSet tataSet = new DataSet();
            //if (tcoids != "0")
            //{
                string xsql = "SELECT t.DepName,t.DepID,COUNT(*) as c FROM (";
            xsql += " select  b.DepName,b.DepID,a.UserID,COUNT(*) as c from TrainCourseUser as a ";
            xsql += " LEFT JOIN Dep as b on a.DeptID=b.DepID where 1=1 and a.TCOID in(" + tcoids + ") GROUP BY b.DepName,b.DepID,a.UserID ";
            xsql += ") as t GROUP BY  t.DepName,t.DepID";
                tataSet = DbHelperSQL.Query(xsql);
            //}
            #endregion

            #region 已达标人员
            DataSet yDataSet = new DataSet();
            //if (tcoids != "0")
            //{
                string ysql = "select t.DepName,t.DepID,COUNT(*) as c from ( ";
            ysql += "select a.UserID,c.DepName,c.DepID from TrainStudyLog as a left JOIN UserInfo as b on a.UserID=b.UserID ";
            ysql += "left JOIN Dep as c on b.DeptID=c.DepID where 1=1 and a.TCOID IN(" + tcoids + ") GROUP BY c.DepName,c.DepID,a.UserID HAVING COUNT(a.UserID)>=4 ";
            ysql += ") as t  GROUP BY t.DepName,t.DepID";
                yDataSet = DbHelperSQL.Query(ysql);
            //}
            #endregion
            List<ReportCourseStudy> ReportCourseStudyList = new List<ReportCourseStudy>();
            List<ReportCourseStudy> ReportCourseStudyDuanList = new List<ReportCourseStudy>();
            #region 总人员 与 达标 未达标人员
            if (tataSet != null && tataSet.Tables[0] != null && tataSet.Tables[0].Rows.Count > 0)
            {
                ReportCourseStudy reportCourseStudy = new ReportCourseStudy();
                foreach (DataRow item in tataSet.Tables[0].Rows)
                {
                    reportCourseStudy = new ReportCourseStudy();
                    bool flag = false;
                    reportCourseStudy.DepName = item["DepName"] == null ? "" : item["DepName"].ToString();
                    reportCourseStudy.DepID= item["DepID"] == null ?0 : Convert.ToInt64(item["DepID"].ToString());
                    reportCourseStudy.xcount= item["c"] == null ? 0 : Convert.ToInt32(item["c"].ToString());
                    if (yDataSet != null && yDataSet.Tables[0] != null && yDataSet.Tables[0].Rows.Count > 0)
                    {
                        foreach (DataRow yitem in yDataSet.Tables[0].Rows)
                        {
                            var tempDepID= yitem["DepID"] == null ? 0 : Convert.ToInt64(yitem["DepID"].ToString());
                            if (reportCourseStudy.DepID== tempDepID)
                            {
                                reportCourseStudy.ycount= yitem["c"] == null ? 0 : Convert.ToInt32(yitem["c"].ToString());
                                reportCourseStudy.wcount = reportCourseStudy.xcount - reportCourseStudy.ycount;
                                ReportCourseStudyList.Add(reportCourseStudy); flag = true;
                                break;
                            }
                        }
                    }
                    if (!flag)
                    {
                        reportCourseStudy.wcount = reportCourseStudy.xcount - reportCourseStudy.ycount;
                        ReportCourseStudyList.Add(reportCourseStudy);
                    }
                }
            }
            #endregion
            if (IsDuan)
            {
                DataSet depDataSet = new DataSet();
                string sql = "select a.DepID as RDepID,a.DepName as RName,b.DepID,b.DepName from Dep as a ";
                sql += "LEFT JOIN Dep as b on a.DepID=b.DepPID ";
                sql += " where a.DepLevel='1' and b.DepLevel='2' ORDER BY RDepID ";
                depDataSet = DbHelperSQL.Query(sql);
                if (depDataSet != null && depDataSet.Tables[0] != null && depDataSet.Tables[0].Rows.Count > 0)
                {
                    long RId = 0;
                    int count = 0;
                    ReportCourseStudy reportCourseStudy = new ReportCourseStudy();
                    foreach (DataRow itemdep in depDataSet.Tables[0].Rows)
                    {
                        long RdepID = itemdep["RDepID"] == null ? 0 : Convert.ToInt64(itemdep["RDepID"].ToString());
                        long DepID = itemdep["DepID"] == null ? 0 : Convert.ToInt64(itemdep["DepID"].ToString());
                        string RName = itemdep["RName"] == null ? "" : itemdep["RName"].ToString();
                        if (RId!=RdepID)
                        {
                            if (RId!=0)
                            {
                                ReportCourseStudyDuanList.Add(reportCourseStudy);
                                reportCourseStudy = new ReportCourseStudy();
                            }
                            reportCourseStudy.DepName = RName;
                            RId = RdepID; 
                        }
                        foreach (var item in ReportCourseStudyList)
                        {
                            if (item.DepID == DepID || item.DepID == RdepID)
                            {
                                reportCourseStudy.xcount += item.xcount;
                                reportCourseStudy.ycount += item.ycount;
                                reportCourseStudy.wcount += item.wcount;
                                item.xcount = 0;
                                item.ycount = 0;
                                item.wcount = 0;
                            }
                        }
                        if (count == depDataSet.Tables[0].Rows.Count - 1)
                        {
                            ReportCourseStudyDuanList.Add(reportCourseStudy);
                        }
                        count++;
                    }
                    
                }
            }
            if (IsDuan) { return ReportCourseStudyDuanList; }
            else
            {
                return ReportCourseStudyList;
            }
        }


        #region ---乘务员参培课程数量以及内容统计 及 未培训人员明细
        //获取车间数据  [车段/车间 调用] 
        public List<ReportCourseCount> GetCourseStudyCourseCount_Chejian(int? DepLevelId, int? CurrentDepID,DateTime startTime,DateTime endTime)
        {
            List<ReportCourseCount> reportCourseStudies = new List<ReportCourseCount>();
            string sqlC = "";
            switch (DepLevelId)
            {
                case 0:
                    sqlC = "select DepID,DepName,DepPID from Dep where DepLevel='1'";
                    break;
                case 1://车间
                    sqlC = "select DepID,DepName,DepPID from Dep where DepID='" + CurrentDepID + "'";
                    break;
                case 2://车队
                    break;
            }
            DataSet chejian = DbHelperSQL.Query(sqlC);
            string chejianIds = "0";
            if (chejian!=null&& chejian.Tables[0]!=null&& chejian.Tables[0].Rows.Count>0)
            {
                var tb = chejian.Tables[0];
                for (int i = 0; i <tb.Rows.Count;i++)
                {
                    var item = tb.Rows[i];
                    var DepID = item["DepID"] == null ? "0" : item["DepID"].ToString();
                    chejianIds +=","+ DepID+ "";
                }
            }
            DataSet chedui = DbHelperSQL.Query("select DepID,DepName,DepPID from Dep where DepPID in ("+chejianIds+ ")");
            string chedui_Ids = "0";
            if (chedui!=null&& chedui.Tables[0]!=null&& chedui.Tables[0].Rows.Count>0)
            {
                var tb = chedui.Tables[0];
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    var item = tb.Rows[i];
                    var DepID = item["DepID"] == null ? "0" : item["DepID"].ToString();
                    chedui_Ids += ","+ DepID + "";
                }
            }
            var cheduiList = GetCourseStudyCourseCount_Chedui(chedui_Ids,startTime,endTime);
            //车间数据
            if (chejian != null && chejian.Tables[0] != null && chejian.Tables[0].Rows.Count > 0)
            {
                var tb = chejian.Tables[0];
                ReportCourseCount reportCourseCount = new ReportCourseCount();
                int cdepId = 0;
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    var item = tb.Rows[i];
                    var depId=item["DepID"] == null ? 0 : Convert.ToInt32(item["DepID"].ToString());
                    if (cdepId != depId)
                    {
                        if (cdepId!=0)
                        {
                            reportCourseStudies.Add(reportCourseCount);
                        }
                        reportCourseCount = new ReportCourseCount();
                        reportCourseCount.DepName = item["DepName"]==null?"":item["DepName"].ToString();
                        reportCourseCount.DepID = depId;
                        cdepId = depId;
                    }
                    foreach (var fit in cheduiList)
                    {
                        if (depId==fit.depPId)
                        {
                            reportCourseCount.x += fit.x;
                            reportCourseCount.x2 += fit.x2;
                        }
                    }
                    if (i==tb.Rows.Count-1)
                    { reportCourseStudies.Add(reportCourseCount); }
                }
            }
            return reportCourseStudies;
        }
        //获取车队数据
        public List<ReportCourseCount> GetCourseStudyCourseCount_Chedui(int? DepLevelId, int? CurrentDepID, DateTime startTime, DateTime endTime)
        {
            List<ReportCourseCount> reportCourseStudies = new List<ReportCourseCount>();
            string sqlC = "";
            DataSet chejian = new DataSet();
            switch (DepLevelId)
            {
                case 0:
                    sqlC = "select DepID,DepName,DepPID from Dep where DepLevel='1'";
                    chejian = DbHelperSQL.Query(sqlC);
                    break;
                case 1://车间
                    sqlC = "select DepID,DepName,DepPID from Dep where DepID='" + CurrentDepID + "'";
                    chejian = DbHelperSQL.Query(sqlC);
                    break;
                case 2://车队
                    break;
            }
            string chejianIds = "0";
           
            DataSet chedui = new DataSet();
            if (DepLevelId>1)
            {
                chedui = DbHelperSQL.Query("select DepID,DepName,DepPID from Dep where DepID='"+CurrentDepID+ "' ");
            }
            else
            {
                if (chejian != null && chejian.Tables[0] != null && chejian.Tables[0].Rows.Count > 0)
                {
                    var tb = chejian.Tables[0];
                    for (int i = 0; i < tb.Rows.Count; i++)
                    {
                        var item = tb.Rows[i];
                        var DepID = item["DepID"] == null ? "0" : item["DepID"].ToString();
                        chejianIds += "," + DepID + "";
                    }
                }
                chedui = DbHelperSQL.Query("select DepID,DepName,DepPID from Dep where DepPID in (" + chejianIds + ")");
            }
            
            string chedui_Ids = "0";
            if (chedui != null && chedui.Tables[0] != null && chedui.Tables[0].Rows.Count > 0)
            {
                var tb = chedui.Tables[0];
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    var item = tb.Rows[i];
                    var DepID = item["DepID"] == null ? "0" : item["DepID"].ToString();
                    chedui_Ids += "," + DepID + "";
                }
            }
            var cheduiList = GetCourseStudyCourseCount_Chedui(chedui_Ids, startTime, endTime);
            int cdepId = 0;

            if (chedui != null && chedui.Tables[0] != null && chedui.Tables[0].Rows.Count > 0)
            {
                var tb = chedui.Tables[0];
                ReportCourseCount reportCourseCount = new ReportCourseCount();
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    var item = tb.Rows[i];
                    int DepID = item["DepID"] == null ? 0 :Convert.ToInt32(item["DepID"].ToString());
                    if (cdepId!=DepID)
                    {
                        if (cdepId!=0)
                        {
                            reportCourseStudies.Add(reportCourseCount);
                        }
                        reportCourseCount = new ReportCourseCount();
                        cdepId = DepID;
                        reportCourseCount.DepID = DepID;
                        reportCourseCount.DepName= item["DepName"] == null ? "" : item["DepName"].ToString();
                    }
                    foreach (var it in cheduiList)
                    {
                        if (it.DepID==DepID)
                        {
                            reportCourseCount.x += it.x;
                            reportCourseCount.x2 += it.x2;
                        }
                    }
                    if (i==tb.Rows.Count-1)
                    {
                        reportCourseStudies.Add(reportCourseCount);
                    }
                }
            }
            return reportCourseStudies;
        }

        //车队数据  
        public List<ReportCourseCount> GetCourseStudyCourseCount_Chedui(string chedui_Ids,DateTime startTime,DateTime endTime)
        {
            List<ReportCourseCount> reportCourseStudies = new List<ReportCourseCount>();
            #region 部门 课程参培次数,培训内容数量
            string sql = "";
            sql += "select a.*,b.c1 from (";
            sql += "select d.DepName,d.DepID,d.DepPID,COUNT(*) c from TrainStudyLog as g ";
            sql += "left join UserInfo as u on g.UserID=u.UserID ";
            sql += "LEFT JOIN Dep as d on u.DeptID=d.DepID ";
            sql += " where 1=1 and g.StudyDateTime BETWEEN '"+startTime+"' and '"+endTime.ToString("yyyy-MM-dd 23:59:59")+"' and d.DepID in (" + chedui_Ids + ") ";
            sql += "GROUP BY d.DepName,d.DepID,d.DepPID ) as a left join (";
            sql += "select d.DepName,d.DepID,d.DepPID,COUNT(DISTINCT t.TContent) c1 from TrainStudyLog as g ";
            sql += "left join UserInfo as u on g.UserID=u.UserID ";
            sql += "LEFT JOIN Dep as d on u.DeptID=d.DepID ";
            sql += "left join TrainCourseFiles as t on g.TCOID=t.TCOID ";
            sql += "where 1=1 and g.StudyDateTime BETWEEN '"+startTime+"' and '"+endTime.ToString("yyyy-MM-dd 23:59:59") + "' and d.DepID in (" + chedui_Ids + ") ";
            sql += "GROUP BY d.DepName,d.DepID,d.DepPID ) as b on a.DepID=b.DepID ";
            DataSet StudyRecorder = DbHelperSQL.Query(sql);
            #endregion
            if (StudyRecorder!=null&& StudyRecorder.Tables[0]!=null&& StudyRecorder.Tables[0].Rows.Count>0)
            {
                var tb = StudyRecorder.Tables[0];
                #region 处理
                for (int i=0;i< tb.Rows.Count;i++)
                {
                    var item = tb.Rows[i];
                    ReportCourseCount reportCourseCount = new ReportCourseCount();
                    string depName = item["DepName"]==null?"": item["DepName"].ToString();
                    int depPId = item["DepPID"] == null ? 0 : Convert.ToInt32(item["DepPID"].ToString());
                    int DepID = item["DepID"] == null ? 0 : Convert.ToInt32(item["DepID"].ToString());
                    int c = item["c"] == null ? 0 : Convert.ToInt32(item["c"].ToString());
                    int c1 = item["c1"] == null ? 0 : Convert.ToInt32(item["c1"].ToString());
                    reportCourseCount.DepName = depName;
                    reportCourseCount.depPId = depPId;
                    reportCourseCount.DepID = DepID;
                    reportCourseCount.x = c;
                    reportCourseCount.x2 = c1;
                    reportCourseStudies.Add(reportCourseCount);
                }
                #endregion
            }
            return reportCourseStudies;
        }

        //乘务员数据  
        public List<ReportCourseCount> GetCourseStudyCourseCount_Chengwuyuan(string chedui_Ids, DateTime startTime, DateTime endTime)
        {
            List<ReportCourseCount> reportCourseStudies = new List<ReportCourseCount>();
            List<ReportCourseCount> result = new List<ReportCourseCount>();
            #region 部门 乘务员课程参培次数,培训内容数量
            string sql = "";
            sql += "select a.*,b.c1 from (";
            sql += "select u.UserName,u.UserID,d.DepName,d.DepID,d.DepPID,COUNT(*) c from TrainStudyLog as g ";
            sql += "left join UserInfo as u on g.UserID=u.UserID ";
            sql += "LEFT JOIN Dep as d on u.DeptID=d.DepID ";
            sql += "where 1=1 and g.StudyDateTime BETWEEN '" + startTime + "' and '" + endTime.ToString("yyyy-MM-dd 23:59:59") + "' and d.DepID in (" + chedui_Ids + ") ";
            sql += "GROUP BY u.UserID,u.UserName,d.DepName,d.DepID,d.DepPID ";
            sql += ") as a left JOIN (";
            sql += "select u.UserName,u.UserID,d.DepName,d.DepID,d.DepPID,COUNT(DISTINCT t.TContent) c1 from TrainStudyLog as g ";
            sql += "left join UserInfo as u on g.UserID=u.UserID ";
            sql += "LEFT JOIN Dep as d on u.DeptID=d.DepID ";
            sql += "left join TrainCourseFiles as t on g.TCOID=t.TCOID ";
            sql += "where 1=1 and g.StudyDateTime BETWEEN '" + startTime + "' and '" + endTime.ToString("yyyy-MM-dd 23:59:59") + "' and d.DepID in (" + chedui_Ids + ") ";
            sql += "GROUP BY u.UserID,u.UserName,d.DepName,d.DepID,d.DepPID ";
            sql += ") as b on a.UserID=b.UserID";
            DataSet StudyRecorder = DbHelperSQL.Query(sql);
            DataSet StudyRecorder2 = DbHelperSQL.Query("select UserID,UserName from UserInfo where RoleID=5 and DeptID in (" + chedui_Ids + ")");
            #endregion
            string uid = "";
            if (StudyRecorder2 != null && StudyRecorder2.Tables[0] != null && StudyRecorder2.Tables[0].Rows.Count > 0)
            {
                var u = StudyRecorder2.Tables[0];
                ReportCourseCount reportCourseCount = new ReportCourseCount();
                for (int k = 0; k < u.Rows.Count; k++)
                {
                    var item = u.Rows[k];
                    var userid = item["UserID"]==null?"": item["UserID"].ToString();
                    if (uid!=userid)
                    {
                        if (uid!="")
                        {
                            result.Add(reportCourseCount);
                        }
                        reportCourseCount = new ReportCourseCount();
                        reportCourseCount.DepName = item["UserName"] == null ? "" : item["UserName"].ToString();
                        reportCourseCount.flagId = userid;
                        uid = userid;
                    }
                    if (StudyRecorder != null && StudyRecorder.Tables[0] != null && StudyRecorder.Tables[0].Rows.Count > 0)
                    {
                        var tb = StudyRecorder.Tables[0];
                        #region 处理
                        for (int i = 0; i < tb.Rows.Count; i++)
                        {
                            var item1 = tb.Rows[i];
                            string depName = item1["UserName"] == null ? "" : item1["UserName"].ToString();
                            int depPId = item1["DepPID"] == null ? 0 : Convert.ToInt32(item1["DepPID"].ToString());
                            int DepID = item1["DepID"] == null ? 0 : Convert.ToInt32(item1["DepID"].ToString());
                            int c = item1["c"] == null ? 0 : Convert.ToInt32(item1["c"].ToString());
                            int c1 = item1["c1"] == null ? 0 : Convert.ToInt32(item1["c1"].ToString());
                            string userid1 = item1["UserID"] == null ? "": item1["UserID"].ToString();
                            if (userid==userid1)
                            {
                                reportCourseCount.x += c;
                                reportCourseCount.x2 += c1;
                            }
                        }
                        #endregion
                    }
                    if (k==u.Rows.Count-1)
                    {
                        result.Add(reportCourseCount);
                    }
                }
            }
            return result;
        }

       //乘务员培训内容 次数
        public List<ReportCourseCount> GetCourseStudyCourseCount_Content(string User_Id, DateTime startTime, DateTime endTime)
        {
            List<ReportCourseCount> reportCourseStudies = new List<ReportCourseCount>();
            #region 部门 乘务员课程参培次数,培训内容数量
            string sql = "";
            sql += "select t.TContent,COUNT(*) c from TrainStudyLog as g ";
            sql += "left join TrainCourseFiles as t on g.TCOID=t.TCOID ";
            sql += "where g.UserID='"+User_Id+ "'  and g.StudyDateTime BETWEEN '"+ startTime+ "' and '"+endTime.ToString("yyyy-MM-dd 23:59:59")+ "' ";
            sql += "group BY t.TContent ";
            DataSet StudyRecorder = DbHelperSQL.Query(sql);
            #endregion
            if (StudyRecorder != null && StudyRecorder.Tables[0] != null && StudyRecorder.Tables[0].Rows.Count > 0)
            {
                var tb = StudyRecorder.Tables[0];
                #region 处理
                ReportCourseCount reportCourseCount = new ReportCourseCount();
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    reportCourseCount = new ReportCourseCount();
                    var item1 = tb.Rows[i];
                    string TContent = item1["TContent"] == null ? "" : item1["TContent"].ToString();
                    int c = item1["c"] == null ? 0 : Convert.ToInt32(item1["c"].ToString());
                    if (string.IsNullOrEmpty(TContent))
                    {
                        continue;
                    }
                    reportCourseCount.DepName = TContent;
                    reportCourseCount.x = c;
                    reportCourseStudies.Add(reportCourseCount);
                }
                #endregion
            }
            return reportCourseStudies;
        }

        //未培训人员明细
        public List<ReportCoursePerson> GetCourseStudyCourse_uncommittedPerson(string chedui_Ids, DateTime startTime, DateTime endTime)
        {
            List<ReportCoursePerson> result = new List<ReportCoursePerson>();
            string sql = "";
            sql += "  select u1.UserID,u1.UserName,UserName,Sex,Post,d.DepName,Groups from UserInfo  as u1 " +
                " left JOIN Dep as d on u1.DeptID = d.DepID " +
                " where UserID not in (" +
                " select distinct UserID from TrainStudyLog t where CONVERT(nvarchar(10), t.StudyDateTime, 120) BETWEEN '"+startTime.ToString("yyyy-MM-dd")+"' and '"+endTime.ToString("yyyy-MM-dd")+"'" +
                " )  " +
                " and DeptID in("+chedui_Ids+") and u1.RoleID = 5 ";

            //sql += "select u1.UserID,u1.UserName,UserName,Sex,Post,d.DepName from UserInfo  as u1 left JOIN Dep as d on u1.DeptID=d.DepID where NOT EXISTS (";
            //sql += "select u.UserID from UserInfo  as u left join  TrainStudyLog as t on u.UserID=t.UserID where DeptID in("+chedui_Ids+ ") and u.RoleID='5'  and u.UserID=u1.UserID and CONVERT(nvarchar(10), t.StudyDateTime,120) BETWEEN '" + startTime+"' and '"+endTime+"'";
            //sql += ")and u1.RoleID='5' and u1.DeptID in(" + chedui_Ids+ ") order by d.DepName,u1.UserName";
            DataSet StudyRecorder = DbHelperSQL.Query(sql);
            if (StudyRecorder != null && StudyRecorder.Tables[0] != null && StudyRecorder.Tables[0].Rows.Count > 0)
            {
                var u = StudyRecorder.Tables[0];
                ReportCoursePerson reportCourseCount = new ReportCoursePerson();
                for (int k = 0; k < u.Rows.Count; k++)
                {
                    reportCourseCount = new ReportCoursePerson();
                    var item = u.Rows[k];
                    reportCourseCount.UserID = item["UserID"] == null ? "" : item["UserID"].ToString();
                    reportCourseCount.UserName = item["UserName"] == null ? "" : item["UserName"].ToString();
                    reportCourseCount.Sex = item["Sex"] == null ? "" : item["Sex"].ToString();
                    reportCourseCount.Post = item["Post"] == null ? "" : item["Post"].ToString();
                    reportCourseCount.DepName = item["DepName"] == null ? "" : item["DepName"].ToString();
                    reportCourseCount.Groups = item["Groups"] == null ? "" : item["Groups"].ToString();
                    result.Add(reportCourseCount);
                }
                reportCourseCount = new ReportCoursePerson();
                //reportCourseCount.DepName = "合计："+ result.Count+"人";
                result.Add(reportCourseCount);
            }
            return result;
        }

        #endregion

        #region ---乘务员学习次数 统计
        //学习次数统计
        public List<ReportCourseCount> GetCourseStudyCourseCount_StudyCount(string chedui_Ids, DateTime startTime, DateTime endTime,string personName)
        {
            var person = "";
            if (!string.IsNullOrEmpty(personName))
            {
                person = " and c.UserName='"+personName+"'";
            }
            int count = 0;
            List<ReportCourseCount> reportCourseStudies = new List<ReportCourseCount>();
            List<ReportCourseCount> result = new List<ReportCourseCount>();
            #region 部门 乘务员学习次数,培训内容数量
            string sql = "";
            sql += "select c.UserName,t.UserID,t.c,d.DepID from ( select g.UserID,c.UserName,count(*) as c from TrainStudyLog as g left join UserInfo as c on g.UserID=c.UserID ";
            sql += "left join Dep as d on c.DeptID=d.DepID ";
            sql += "where 1=1 and c.RoleID='5'  "+ person + " and StudyDateTime BETWEEN '" + startTime + "' and '" + endTime.ToString("yyyy-MM-dd 23:59:59") + "' and c.DeptID in ("+chedui_Ids+")  group BY g.UserID,c.UserName ";
            sql += "HAVING COUNT(*)>"+count+ " ) as t left join UserInfo as c on t.UserID=c.UserID left join Dep as d on c.DeptID=d.DepID";
            DataSet StudyRecorder = DbHelperSQL.Query(sql);
            if (!string.IsNullOrEmpty(personName))
            {
                person = " and UserName='" + personName + "'";
            }
            DataSet StudyRecorder2 = DbHelperSQL.Query("select UserID,UserName from UserInfo where RoleID=5 and DeptID in (" + chedui_Ids + ") "+ person + "");
            #endregion
            string uid = "";
            if (StudyRecorder2 != null && StudyRecorder2.Tables[0] != null && StudyRecorder2.Tables[0].Rows.Count > 0)
            {
                var u = StudyRecorder2.Tables[0];
                ReportCourseCount reportCourseCount = new ReportCourseCount();
                for (int k = 0; k < u.Rows.Count; k++)
                {
                    var item = u.Rows[k];
                    var userid = item["UserID"] == null ? "" : item["UserID"].ToString();
                    if (uid != userid)
                    {
                        if (uid != "")
                        {
                            result.Add(reportCourseCount);
                        }
                        reportCourseCount = new ReportCourseCount();
                        reportCourseCount.DepName = item["UserName"] == null ? "" : item["UserName"].ToString();
                        reportCourseCount.flagId = userid;
                        uid = userid;
                    }
                    if (StudyRecorder != null && StudyRecorder.Tables[0] != null && StudyRecorder.Tables[0].Rows.Count > 0)
                    {
                        var tb = StudyRecorder.Tables[0];
                        #region 处理
                        for (int i = 0; i < tb.Rows.Count; i++)
                        {
                            var item1 = tb.Rows[i];
                            string depName = item1["UserName"] == null ? "" : item1["UserName"].ToString();
                            int DepID = item1["DepID"] == null ? 0 : Convert.ToInt32(item1["DepID"].ToString());
                            int c = item1["c"] == null ? 0 : Convert.ToInt32(item1["c"].ToString());
                            string userid1 = item1["UserID"] == null ? "" : item1["UserID"].ToString();
                            if (userid == userid1)
                            {
                                reportCourseCount.x += c;
                            }
                        }
                        #endregion
                    }
                    if (k == u.Rows.Count - 1)
                    {
                        result.Add(reportCourseCount);
                    }
                }
            }
            return result.OrderByDescending(x=>x.x).ToList();
        }
        //统计不足次数的 乘务员 学习次数
        public List<ReportCourseCount> GetCourseStudyCourseCount_CountNo(string chedui_Ids, DateTime startTime, DateTime endTime,string personName)
        {
            var person = "";
            if (!string.IsNullOrEmpty(personName))
            {
                person = " and c.UserName='" + personName + "'";
            }
            int count = 0;
            List<ReportCourseCount> reportCourseStudies = new List<ReportCourseCount>();
            List<ReportCourseCount> result = new List<ReportCourseCount>();

            #region 部门 乘务员学习次数,培训内容数量
            string sql = "";
            sql += "select c.UserName,t.UserID,t.c,d.DepID from ( select g.UserID,c.UserName,count(*) as c from TrainStudyLog as g left join UserInfo as c on g.UserID=c.UserID ";
            sql += "left join Dep as d on c.DeptID=d.DepID ";
            sql += "where 1=1 and c.RoleID='5'  " + person + " and StudyDateTime BETWEEN '" + startTime + "' and '" + endTime.ToString("yyyy-MM-dd 23:59:59") + "' and c.DeptID in (" + chedui_Ids + ")  group BY g.UserID,c.UserName ";
            sql += "HAVING COUNT(*)>" + count + " ) as t left join UserInfo as c on t.UserID=c.UserID left join Dep as d on c.DeptID=d.DepID";
            DataSet StudyRecorder = DbHelperSQL.Query(sql);
            if (!string.IsNullOrEmpty(personName))
            {
                person = " and UserName='" + personName + "'";
            }
            DataSet StudyRecorder2 = DbHelperSQL.Query("select UserID,UserName from UserInfo where DeptID in (" + chedui_Ids + ") "+ person + "");
            #endregion
            string uid = "";
            if (StudyRecorder2 != null && StudyRecorder2.Tables[0] != null && StudyRecorder2.Tables[0].Rows.Count > 0)
            {
                var u = StudyRecorder2.Tables[0];
                ReportCourseCount reportCourseCount = new ReportCourseCount();
                for (int k = 0; k < u.Rows.Count; k++)
                {
                    var item = u.Rows[k];
                    var userid = item["UserID"] == null ? "" : item["UserID"].ToString();
                    if (uid != userid)
                    {
                        if (uid != "")
                        {
                            if (reportCourseCount.x<4)
                            {
                                result.Add(reportCourseCount);
                            }
                        }
                        reportCourseCount = new ReportCourseCount();
                        reportCourseCount.DepName = item["UserName"] == null ? "" : item["UserName"].ToString();
                        reportCourseCount.flagId = userid;
                        uid = userid;
                    }
                    if (StudyRecorder != null && StudyRecorder.Tables[0] != null && StudyRecorder.Tables[0].Rows.Count > 0)
                    {
                        var tb = StudyRecorder.Tables[0];
                        #region 处理
                        for (int i = 0; i < tb.Rows.Count; i++)
                        {
                            var item1 = tb.Rows[i];
                            string depName = item1["UserName"] == null ? "" : item1["UserName"].ToString();
                            int DepID = item1["DepID"] == null ? 0 : Convert.ToInt32(item1["DepID"].ToString());
                            int c = item1["c"] == null ? 0 : Convert.ToInt32(item1["c"].ToString());
                            string userid1 = item1["UserID"] == null ? "" : item1["UserID"].ToString();
                            if (userid == userid1)
                            {
                                reportCourseCount.x += c;
                            }
                        }
                        #endregion
                    }
                    if (k == u.Rows.Count - 1)
                    {
                        if (reportCourseCount.x < 4)
                        {
                            result.Add(reportCourseCount);
                        }
                    }
                }
            }
            return result.OrderByDescending(x =>x.x).ToList();
        }
        //某个乘务员 学习课程详细 及 次数
        public List<ReportCourseCount> GetCourseStudyCourseCount_ChengwuyuanCourseDetailCount(string User_Id, DateTime startTime, DateTime endTime)
        {
            List<ReportCourseCount> result = new List<ReportCourseCount>();
            #region 部门 乘务员学习次数,培训内容数量
            string sql = "";
            sql += "select r.TCOName,CONVERT(varchar(11),StudyDateTime,20)  as StudyDateTime,count(*) c from TrainStudyLog as g left join TrainCourse as r on g.TCOID=r.TCOID ";
            sql += "where 1=1 and StudyDateTime BETWEEN '"+startTime+"' and '"+ endTime.ToString("yyyy-MM-dd 23:59:59") + "' and g.UserID='"+User_Id+"' ";
            sql += "GROUP BY r.TCOName,CONVERT(varchar(11),StudyDateTime,20) ";
            DataSet StudyRecorder = DbHelperSQL.Query(sql);
            #endregion
            if (StudyRecorder != null && StudyRecorder.Tables[0] != null && StudyRecorder.Tables[0].Rows.Count > 0)
            {
                var tb = StudyRecorder.Tables[0];
                #region 处理
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    ReportCourseCount reportCourseCount = new ReportCourseCount();
                    var item1 = tb.Rows[i];
                    int c = item1["c"] == null ? 0 : Convert.ToInt32(item1["c"].ToString());
                    reportCourseCount.x = c;
                    reportCourseCount.DepName = item1["TCOName"] == null ? "" : item1["TCOName"].ToString();
                    reportCourseCount.StudyDateTime = item1["StudyDateTime"] == null ? "" : item1["StudyDateTime"].ToString();
                    result.Add(reportCourseCount);
                }
                #endregion
            }
            return result;
        }

        //车队信息列表
        public List<ReportCourseCount> Get_CheduiList(int? DepLevelId, int? CurrentDepID)
        {
            List<ReportCourseCount> reportCourseStudies = new List<ReportCourseCount>();
            DataSet chejian = new DataSet();
            DataSet chedui = new DataSet();
            string sqlC = "";
            switch (DepLevelId)
            {
                case 0:
                    sqlC = "select DepID,DepName,DepPID from Dep where DepLevel='1'";
                    break;
                case 1://车间
                    sqlC = "select DepID,DepName,DepPID from Dep where DepID='" + CurrentDepID + "'";
                    break;
                case 2://车队
                    break;
            }
        
            if (DepLevelId<=1)
            {
                chejian = DbHelperSQL.Query(sqlC);
                string chejianIds = "0";
                if (chejian != null && chejian.Tables[0] != null && chejian.Tables[0].Rows.Count > 0)
                {
                    var tb = chejian.Tables[0];
                    for (int i = 0; i < tb.Rows.Count; i++)
                    {
                        var item = tb.Rows[i];
                        var DepID = item["DepID"] == null ? "0" : item["DepID"].ToString();
                        chejianIds += "," + DepID + "";
                    }
                }
                chedui = DbHelperSQL.Query("select DepID,DepName,DepPID from Dep where DepPID in (" + chejianIds + ")");
            }
            else
            {
                chedui = DbHelperSQL.Query("select DepID,DepName,DepPID from Dep where DepID='"+CurrentDepID+"'");
            }
            //车间数据
            if (chedui != null && chedui.Tables[0] != null && chedui.Tables[0].Rows.Count > 0)
            {
                var tb = chedui.Tables[0];
                ReportCourseCount reportCourseCount = new ReportCourseCount();
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    reportCourseCount = new ReportCourseCount();
                    var item = tb.Rows[i];
                    reportCourseCount.DepName = item["DepName"] == null ? "" : item["DepName"].ToString();
                    reportCourseCount.DepID = item["DepID"] == null ? 0 : Convert.ToInt32(item["DepID"].ToString());
                    reportCourseStudies.Add(reportCourseCount);
                }
            }
            return reportCourseStudies;
        }

        //获取车间列表
        public List<ReportCourseCount> Get_ChejianList(int? DepLevelId, int? CurrentDepID)
        {
            List<ReportCourseCount> reportCourseStudies = new List<ReportCourseCount>();
            DataSet chejian = new DataSet();
            DataSet chedui = new DataSet();
            string sqlC = "";
            switch (DepLevelId)
            {
                case 0: //机务段
                    sqlC = "select DepID,DepName,DepPID from Dep where DepLevel='1'";
                    break;
                case 1://车间
                    sqlC = "select DepID,DepName,DepPID from Dep where DepID='" + CurrentDepID + "'";
                    break;
                case 2://车队
                    break;
            }
            chejian = DbHelperSQL.Query(sqlC);

            //车间数据
            if (chejian != null && chejian.Tables[0] != null && chejian.Tables[0].Rows.Count > 0)
            {
                var tb = chejian.Tables[0];
                ReportCourseCount reportCourseCount = new ReportCourseCount();
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    reportCourseCount = new ReportCourseCount();
                    var item = tb.Rows[i];
                    reportCourseCount.DepName = item["DepName"] == null ? "" : item["DepName"].ToString();
                    reportCourseCount.DepID = item["DepID"] == null ? 0 : Convert.ToInt32(item["DepID"].ToString());
                    reportCourseStudies.Add(reportCourseCount);
                }
            }
            return reportCourseStudies;
        }



        #endregion

        #region 记录查询
        //学习某个课程的 乘务员及学习记录
        public List<ReportCourseCount> GetCourseStudyCourseCount_ChengwuyuanStudyCourseRecorder(string TCOID)
        {
            List<ReportCourseCount> result = new List<ReportCourseCount>();
            #region 学习了某个课程的 乘务员 及 学习记录
            string sql = "";
            sql += "SELECT u.UserName,g.StudyDateTime from TrainStudyLog as g  ";
            sql += "left join UserInfo as u  on g.UserID=u.UserID where g.TCOID='"+TCOID+"'";
            DataSet StudyRecorder = DbHelperSQL.Query(sql);
            #endregion
            if (StudyRecorder != null && StudyRecorder.Tables[0] != null && StudyRecorder.Tables[0].Rows.Count > 0)
            {
                var tb = StudyRecorder.Tables[0];
                #region 处理
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    ReportCourseCount reportCourseCount = new ReportCourseCount();
                    var item1 = tb.Rows[i];
                    reportCourseCount.DepName = item1["UserName"] == null ? "" : item1["UserName"].ToString();
                    reportCourseCount.flagId = item1["StudyDateTime"] == null ? "" : item1["StudyDateTime"].ToString();
                    result.Add(reportCourseCount);
                }
                #endregion
            }
            return result;
        }

        #endregion


        public List<long> AddList(IPR_StudyMeetingMrgSys.Model.TrainCourse item)
        {
            var list = new List<long>();
            List<string> sqlList = new List<string>();
            var listdata = new List<IPR_StudyMeetingMrgSys.Model.TrainCourse>();
            DateTime startTime =Convert.ToDateTime(Convert.ToDateTime(item.StartDateTime.ToString()).ToString("yyyy-MM-dd HH:mm"));
            DateTime endTime = Convert.ToDateTime(Convert.ToDateTime(item.EndDateTime.ToString()).ToString("yyyy-MM-dd HH:mm"));
            DateTime star = Convert.ToDateTime(Convert.ToDateTime(item.StartDateTime.ToString()).ToString("yyyy-MM-dd"));
            DateTime end = Convert.ToDateTime(Convert.ToDateTime(item.EndDateTime.ToString()).ToString("yyyy-MM-dd"));
            int c = new TimeSpan(end.Ticks).Subtract(new TimeSpan(star.Ticks)).Days; 
            for (int i=0;i<=c; i++)
            {
                if (i>0) { item.TCOID += 1;}
                list.Add(item.TCOID);
                DateTime t1 = startTime.AddDays(i);
                DateTime t2 = endTime.AddDays(i - c);
                item.StartDateTime = t1;
                item.EndDateTime =t2 ;
                item.Duration = Convert.ToInt32(new TimeSpan(t2.Ticks).Subtract(new TimeSpan(t1.Ticks)).TotalMinutes);
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into TrainCourse(");
                strSql.Append("TPID,TCID,TCOID,TrainTeacherID,TrainTeacherName,StartDateTime,EndDateTime,Duration,DepID,IsCheck,IsRelease,AddUserID,AddUserName,AddDateTime,IsUse,DepLevel,SendDepID,CRID,CRName,TCOName)");
                strSql.Append(" values (");
                strSql.Append("'"+item.TPID+"','"+item.TCID + "','"+item.TCOID + "','"+item.TrainTeacherID + "','"+ item.TrainTeacherName + "','"+item.StartDateTime + "','"+item.EndDateTime + "','"+item.Duration + "','"+item.DepID + "','"+item.IsCheck + "','"+item.IsRelease + "','"+item.AddUserID + "','"+item.AddUserName + "','"+item.AddDateTime + "','"+item.IsUse + "','"+item.DepLevel + "','"+item.SendDepID + "','"+item.CRID + "','"+item.CRName + "','"+item.TCOName + "')");
                sqlList.Add(strSql.ToString());
            }
            int rows = DbHelperSQL.ExecuteSqlTran(sqlList);
            return list;
        }

        #endregion  ExtensionMethod
    }

}

